package com.ruge.test.commons.dbutils;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * 爱丽丝、如歌  创建于 2018/6/16 10:26
 * 说明:  批处理
 */
public class Batch {
	/**
	 * 批量删除
	 */
	@Test
	public void test1() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		String ids = "8,9";
		conn.setAutoCommit(false);
		QueryRunner runner = new QueryRunner();
		Object[][] params = new Object[ids.split(",").length][];//高维确定执行sql语句的次数，低维是给？赋值
		for (int i = 0; i < params.length; i++) {
			params[i] = new Object[]{ids.split(",")[i]};//给“？”赋值
		}
		String  sql = "delete from sys_user where  id = ?";
		int[] batch = runner.batch(conn, sql, params);
		for (int a:batch
			 ) {
			System.out.println(a);
		}
		DbUtils.commitAndClose(conn);
	}

	/**
	 * 批量更新
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	@Test
	public void test2() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		String ids = "8,9";
		conn.setAutoCommit(false);
		QueryRunner runner = new QueryRunner();
		Object[][] params = new Object[ids.split(",").length][];//高维确定执行sql语句的次数，低维是给？赋值
		for (int i = 0; i < params.length; i++) {
			params[i] = new Object[]{ids.split(",")[i]};//给“？”赋值
		}
		String  sql = "update sys_user set user_mobile = ? where  id = ?";
		int[] batch = runner.batch(conn, sql, params);
		for (int a:batch
		) {
			System.out.println(a);
		}
		DbUtils.commitAndClose(conn);
	}

	/**
	 * 批量插入
	 */
	@Test
	public void batchTest() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ruge_security?serverTimezone=UTC", "root", "root");
		QueryRunner qr = new QueryRunner();
		String sql = "insert into sys_user(user_name, pass_word) values(?,?)";
		Object[][] param= new Object[10][];
		for(int i=0; i<10; i++){
			param[i]= new Object[2];
			param[i][0] = "name"+i*10+0;
			param[i][1] = i*10+1;
		}
		qr.batch(conn,sql, param);
	}


}
